package cvs;
import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class CubeCSV {
	public static int max_sales_period = 201005;
	public static int this_year = 2010;

	public static int min_year = 2007;
	public static int max_year = 2010;

	public static boolean annual = false;

	// public static String[] products = { "All", "PC", "Printer", "Display" };
	public static String[] products = { "All" };

	public static void main(String[] args) {
		System.out.println("start---");

		boolean stop = false;
		if (stop)
			return;

		System.out.println("------- tier 2");
		buildCVSAllYearsT2("Tier_2_");
		System.out.println("---end tier 2 ");

		System.out.println("------- tier 3");
		buildCVSAllYearsT3("Tier_3_");
		System.out.println("finished---");
	}

	public static String buildSql2(String product, int year) {
		Sql sql = new Sql();

		sql.appendln("select ");
		sql.appendln("x.Year, ");
		sql.appendln("x.Month, ");
		sql.appendln("x.YearMonth, ");
		sql.appendln("x.[Source Name], ");
		sql.appendln("x.[Channel(Disty)], ");
		sql
				.appendln("ISNULL(sa.company_name, x.disty) as 'Disty Company Name', ");
		sql.appendln("x.[Product family], ");
		sql.appendln("x.[Product category], ");
		sql.appendln("x.[Product subcategory], ");
		sql.appendln("x.P_master_id, ");
		sql.appendln("sum(x.Qty) as Qty, ");
		sql.appendln("sum(x.[Sales amount]) as [Sales amount] , ");
		sql.appendln("x.[Channel(Reseller)], ");
		sql.appendln("x.Customer_Name, ");
		sql.appendln("ISNULL(ba.city, x.Customer_city) as 'Reseller City', ");
		sql
				.appendln("ISNULL(ba.state, x.reseller_state) as 'Reseller State', ");
		sql
				.appendln("ISNULL(ba.zipcode, x.reseller_zipcode) as 'Reseller zipcode', ");
		sql
				.appendln("ISNULL(ba.title1, 'Not Defined') as 'Industry Level 1', ");
		sql
				.appendln("ISNULL(ba.title2, 'Not Defined') as 'Industry Level 2', ");
		sql
				.appendln("ISNULL(ba.title3, 'Not Defined') as 'Industry Level 3', ");
		sql.appendln("x.[Product code] ");
		sql.appendln("from ");
		sql.appendln("( ");
		sql.appendln("	select ");
		sql.appendln("	s.period_year as Year ");
		sql.appendln("	,s.period_month as Month ");
		sql.appendln("	,s.period_year*100 + s.period_month as 'YearMonth' ");
		sql.appendln("	,so.name as 'Source Name' ");
		sql.appendln("	,ISNULL(so.channel, 'Others') as 'Channel(Disty)' ");
		sql.appendln("	,p.family as 'Product family' ");
		sql.appendln("	,p.category as 'Product category' ");
		sql.appendln("	,p.subcategory as 'Product subcategory' ");
		sql.appendln("	,p.master_id as P_master_id ");
		sql.appendln("	,sum(s.sales_quantity) as 'Qty' ");
		sql.appendln("	,sum(s.sales_amount) as 'Sales amount' ");
		sql
				.appendln("	,ISNULL(buy_cm.channel, 'Others') as 'Channel(Reseller)' ");
		sql.appendln("	,buy_cm.customer_name as Customer_Name ");
		sql.appendln("	,p2.product_code as 'Product code' ");
		sql.appendln("	,s.seller_cid ");
		sql.appendln("	,s.buyer_cid ");
		sql.appendln("	,sell_c.customer_name as disty ");
		sql.appendln("	,buy_cm.city as Customer_city ");
		sql.appendln("	,buy_cm.state as reseller_state ");
		sql.appendln("	,buy_cm.zipcode as reseller_zipcode ");
		sql.appendln("	from ");
		sql
				.appendln("	SALES s, CUSTOMER sell_c, CUSTOMER buy_c, PRODUCT p,  CUSTOMER buy_cm, SALES_FILE sf, SOURCE so, product p2 ");
		sql.appendln("	where ");

		if (annual) {
			sql.appendln("	s.period_year = " + year);
			if (year == this_year)
				sql.appendln("	and sf.sales_period <= '"
						+ CubeCSV.max_sales_period + "' ");

		} else {
			sql.appendln("	s.period_year >= " + year);
			sql.appendln("	and sf.sales_period <= '" + CubeCSV.max_sales_period
					+ "' ");
		}

		sql.appendln("	and s.tier=2 ");
		sql.appendln("	and s.seller_cid=sell_c.id ");
		sql.appendln("	and s.buyer_cid=buy_c.id ");
		sql.appendln("	and buy_c.master_id= buy_cm.id ");
		sql.appendln("	and buy_cm.id not in ( ");
		sql.appendln("		'6043', ");
		sql.appendln("		'11533', ");
		sql.appendln("		'33818', ");
		sql.appendln("		'117687', ");
		sql.appendln("		'234144', ");
		sql.appendln("		'498055' ");
		sql.appendln("	) ");
		sql.appendln("	and s.product_id=p.id ");
		sql.appendln("	and p.family is not NULL ");

		if (!product.equals("All"))
			sql.appendln("    and p.family = '" + product + "' ");

		sql.appendln("	and p.master_id=p2.id ");
		sql.appendln("	and s.sales_file_id = sf.id ");
		sql.appendln("	and sf.source_id = so.id ");

		sql.appendln("	group by  ");
		sql.appendln("	s.period_year, ");
		sql.appendln("	s.period_month, ");
		sql.appendln("	so.name, ");
		sql.appendln("	so.channel, ");
		sql.appendln("	p.family, ");
		sql.appendln("	p.category, ");
		sql.appendln("	p.subcategory, ");
		sql.appendln("	p.master_id, ");
		sql.appendln("	buy_cm.channel, ");
		sql.appendln("	buy_cm.customer_name, ");
		sql.appendln("	p2.product_code, ");
		sql.appendln("	s.seller_cid, ");
		sql.appendln("	s.buyer_cid, ");
		sql.appendln("	sell_c.customer_name, ");
		sql.appendln("	buy_cm.city, ");
		sql.appendln("	buy_cm.state, ");
		sql.appendln("	buy_cm.zipcode ");

		sql.appendln(")x ");
		sql
				.appendln("left outer join custom_fact_nacis_all sa on x.seller_cid = sa.customer_id ");
		sql
				.appendln("left outer join custom_fact_nacis_all ba on x.buyer_cid = ba.customer_id ");
		sql.appendln("group by ");
		sql.appendln("x.Year, ");
		sql.appendln("x.Month, ");
		sql.appendln("x.YearMonth, ");
		sql.appendln("x.[Source Name], ");
		sql.appendln("x.[Channel(Disty)], ");
		sql.appendln("sa.company_name, ");
		sql.appendln("x.disty, ");
		sql.appendln("x.[Product family], ");
		sql.appendln("x.[Product category], ");
		sql.appendln("x.[Product subcategory], ");
		sql.appendln("x.P_master_id, ");
		sql.appendln("x.[Channel(Reseller)], ");
		sql.appendln("x.Customer_Name, ");
		sql.appendln("ba.city, ");
		sql.appendln("x.Customer_city, ");
		sql.appendln("ba.state, ");
		sql.appendln("x.reseller_state, ");
		sql.appendln("ba.zipcode, ");
		sql.appendln("x.reseller_zipcode, ");
		sql.appendln("ba.title1, ");
		sql.appendln("ba.title2, ");
		sql.appendln("ba.title3, ");
		sql.appendln("x.[Product code] ");

		return sql.toString();
	}

	public static String buildSql2_old(String product, int year) {
		Sql sql = new Sql();

		sql.appendln("select T1.*, ");
		sql
				.appendln("       T2.product_code as 'Product code' -- Base product code ");
		sql.appendln("from ( ");
		sql.appendln("select ");
		sql.appendln("x.year as 'Year', ");
		sql.appendln("x.month as 'Month', ");
		sql.appendln("x.year*100+x.month as 'YearMonth', ");
		sql.appendln("x.name as 'Source Name', ");
		sql.appendln("ISNULL(x.Disty_channel, 'Others') as 'Channel(Disty)', ");
		sql
				.appendln("ISNULL(sa.company_name, x.disty) as 'Disty Comapny Name', ");
		sql.appendln("x.Product_Family as 'Product family', ");
		sql.appendln("x.Product_Category as 'Product category', ");
		sql.appendln("x.Product_Sub_Category as 'Product subcategory', ");
		sql.appendln("x.master_id as P_master_id, ");
		sql.appendln("x.sales_quantity as 'Qty', ");
		sql.appendln("x.sales_amount as 'Sales amount',  ");
		sql
				.appendln("ISNULL(x.reseller_channel, 'Others') as 'Channel(Reseller)', ");
		sql
				.appendln("x.Customer_Name, -- to show common name, using customer(base)name ");
		sql.appendln("ISNULL(ba.city, x.Customer_city) as 'Reseller City', ");
		sql
				.appendln("ISNULL(ba.state, x.reseller_state) as 'Reseller State', ");
		sql
				.appendln("ISNULL(ba.zipcode, x.reseller_zipcode) as 'Reseller zipcode', ");
		sql
				.appendln("ISNULL(ba.title1, 'Not Defined') as 'Industry Level 1', ");
		sql
				.appendln("ISNULL(ba.title2, 'Not Defined') as 'Industry Level 2', ");
		sql.appendln("ISNULL(ba.title3, 'Not Defined') as 'Industry Level 3' ");
		sql.appendln("from ");
		sql.appendln("( ");
		sql.appendln("select ");
		sql.appendln("     so.name, ");
		sql.appendln("     so.channel as Disty_channel, ");
		sql.appendln("     s.period_year as year ");
		sql.appendln("    ,s.period_month as month ");
		sql.appendln("    ,s.seller_cid as seller ");
		sql.appendln("    ,sell_c.customer_name as disty ");
		sql.appendln("    ,s.buyer_cid as buyer ");
		sql.appendln("    ,buy_c.id as buyer_id ");
		sql.appendln("    ,buy_cm.id as buyer_base_id -- temp ");
		sql.appendln("    ,buy_cm.customer_name as Customer_Name ");
		sql.appendln("    ,buy_cm.channel as reseller_channel ");
		sql.appendln("    ,buy_cm.city as Customer_city ");
		sql.appendln("    ,buy_cm.state as reseller_state ");
		sql.appendln("    ,buy_cm.zipcode as reseller_zipcode ");
		sql.appendln("    ,p.product_code ");
		sql.appendln("    ,p.master_id ");
		sql.appendln("    ,p.family as Product_Family ");
		sql.appendln("    ,p.category as Product_Category ");
		sql.appendln("    ,p.subcategory as Product_Sub_Category ");
		sql.appendln("    ,s.sales_quantity ");
		sql.appendln("    ,s.sales_amount ");
		sql
				.appendln("from SALES s, CUSTOMER sell_c, CUSTOMER buy_c, PRODUCT p,  CUSTOMER buy_cm, ");
		sql.appendln("     SALES_FILE sf, SOURCE so  ");

		sql.appendln("where   s.period_year = " + year);

		if (year == this_year)
			sql.appendln("    and sf.sales_period <= '"
					+ CubeCSV.max_sales_period + "' ");

		sql.appendln("    and s.tier=2 ");
		sql.appendln("    and s.seller_cid=sell_c.id ");
		sql.appendln("    and s.buyer_cid=buy_c.id    ");
		sql.appendln("    and buy_c.master_id= buy_cm.id ");
		sql.appendln("    and buy_cm.id not in ( ");
		sql.appendln("    '6043', ");
		sql.appendln("    '11533', ");
		sql.appendln("    '33818', ");
		sql.appendln("    '117687', ");
		sql.appendln("    '234144', ");
		sql.appendln("    '498055' ");
		sql.appendln("    ) ");
		sql.appendln("    and s.product_id=p.id ");
		sql.appendln("    and p.family is not NULL ");

		if (!product.equals("All"))
			sql.appendln("    and p.family = '" + product + "' ");

		sql.appendln("    and s.sales_file_id = sf.id ");
		sql.appendln("    and sf.source_id = so.id ");
		sql.appendln(")x ");
		sql
				.appendln("left outer join custom_fact_nacis_all sa on x.seller = sa.customer_id ");
		sql
				.appendln("left outer join custom_fact_nacis_all ba on x.buyer = ba.customer_id ");
		sql.appendln("--where ba.nacis is not NULL ");
		sql.appendln(") T1, ");
		sql.appendln("epos.dbo.product T2 ");
		sql.appendln("where T1.P_master_id = T2.id ");

		return sql.toString();
	}

	public static String buildSql3(String product, int year) {
		Sql sql = new Sql();

		sql.appendln("select ");
		sql.appendln("	x.Year,  ");
		sql.appendln("	x.Month,  ");
		sql.appendln("	x.YearMonth, ");
		sql
				.appendln("	ISNULL(sa.company_name, x.customer_name) as 'Reseller Name',  ");
		sql.appendln("	x.[Product family],  ");
		sql.appendln("	x.[Product category],  ");
		sql.appendln("	x.[Product subcategory],  ");
		sql.appendln("	x.P_master_id, ");
		sql.appendln("	sum(x.Qty) as Qty,  ");
		sql.appendln("	sum(x.[Sales amount]) as 'Sales amount', ");
		sql
				.appendln("	ISNULL(ba.company_name, x.customer_name) as 'End Customer Name',  ");
		sql
				.appendln("	ISNULL(ba.city, x.Customer_city) as 'End Customer City',  ");
		sql
				.appendln("	ISNULL(ba.state, x.End_Customer_state) as 'End Customer State',  ");
		sql
				.appendln("	ISNULL(ba.zipcode, x.End_Customer_zipcode) as 'End Customer zipcode',  ");
		sql
				.appendln("	ISNULL(ba.title1, 'Not Defined') as 'Industry Level 1',  ");
		sql
				.appendln("	ISNULL(ba.title2, 'Not Defined') as 'Industry Level 2',  ");
		sql
				.appendln("	ISNULL(ba.title3, 'Not Defined') as 'Industry Level 3', ");
		sql.appendln("	x.[Product code] ");
		sql.appendln("from  ");
		sql.appendln("(  ");
		sql.appendln("select ");
		sql.appendln("	s.period_year as Year  ");
		sql.appendln("	,s.period_month as Month  ");
		sql.appendln("	,s.period_year * 100 + s.period_month as 'YearMonth'  ");
		sql.appendln("	,p.family as 'Product family' ");
		sql.appendln("	,p.category as 'Product category'  ");
		sql.appendln("	,p.subcategory as 'Product subcategory' ");
		sql.appendln("	,p.master_id as P_master_id ");
		sql.appendln("	,sum(s.sales_quantity) as 'Qty' ");
		sql.appendln("	,sum(s.sales_amount) as 'Sales amount' ");
		sql.appendln("	,s.seller_cid  ");
		sql.appendln("	,s.buyer_cid  ");
		sql.appendln("	,buy_c.customer_name ");
		sql.appendln("	,buy_c.city as Customer_city  ");
		sql.appendln("	,buy_c.state as End_Customer_state  ");
		sql.appendln("	,buy_c.zipcode as End_Customer_zipcode ");
		sql.appendln("	,p2.product_code as 'Product code' ");
		sql
				.appendln("from SALES s, CUSTOMER sell_c, CUSTOMER buy_c, PRODUCT p, PRODUCT p2 ");
		sql.appendln("where    ");

		if (annual) {
			sql.appendln("	s.period_year = " + year);
			if (year == this_year) {
				sql.appendln("	and s.period_year* 100 + s.period_month <= "
						+ CubeCSV.max_sales_period + " ");
			}
		} else {
			sql.appendln("	s.period_year >= " + year);
			sql.appendln("	and s.period_year* 100 + s.period_month <= "
					+ CubeCSV.max_sales_period + " ");
		}

		sql.appendln("	and s.tier=3  ");
		sql.appendln("	and s.seller_cid=sell_c.id  ");
		sql.appendln("	and s.buyer_cid=buy_c.id  ");
		sql.appendln("	and s.product_id=p.id  ");
		sql.appendln("	and p.family is not NULL ");

		if (!product.equals("All"))
			sql.appendln("	and p.family = '" + product + "' ");

		sql.appendln("	and p.master_id=p2.id ");

		sql.appendln("	group by  ");
		sql.appendln("	s.period_year ");
		sql.appendln("	,s.period_month ");
		sql.appendln("	,p.family ");
		sql.appendln("	,p.category ");
		sql.appendln("	,p.subcategory ");
		sql.appendln("	,p.master_id ");
		sql.appendln("	,s.seller_cid  ");
		sql.appendln("	,s.buyer_cid  ");
		sql.appendln("	,buy_c.customer_name ");
		sql.appendln("	,buy_c.city ");
		sql.appendln("	,buy_c.state ");
		sql.appendln("	,buy_c.zipcode ");
		sql.appendln("	,p2.product_code ");

		sql.appendln(")x ");
		sql
				.appendln("left outer join custom_fact_nacis_all sa on x.seller_cid = sa.customer_id  ");
		sql
				.appendln("left outer join custom_fact_nacis_all ba on x.buyer_cid = ba.customer_id   ");
		sql.appendln("--where ba.nacis is not NULL ");
		sql.appendln("group by ");
		sql.appendln("	x.Year,  ");
		sql.appendln("	x.Month,  ");
		sql.appendln("	x.YearMonth, ");
		sql.appendln("	sa.company_name, ");
		sql.appendln("	x.customer_name, ");
		sql.appendln("	x.[Product family],  ");
		sql.appendln("	x.[Product category],  ");
		sql.appendln("	x.[Product subcategory],  ");
		sql.appendln("	x.P_master_id, ");
		sql.appendln("	ba.company_name, ");
		sql.appendln("	x.customer_name,  ");
		sql.appendln("	ba.city, ");
		sql.appendln("	x.Customer_city,  ");
		sql.appendln("	ba.state, ");
		sql.appendln("	x.End_Customer_state,  ");
		sql.appendln("	ba.zipcode, ");
		sql.appendln("	x.End_Customer_zipcode,  ");
		sql.appendln("	ba.title1, ");
		sql.appendln("	ba.title2, ");
		sql.appendln("	ba.title3, ");
		sql.appendln("	x.[Product code] ");

		return sql.toString();
	}

	public static String buildSql3_old(String product, int year) {
		Sql sql = new Sql();

		sql.appendln("select T1.*,  ");
		sql
				.appendln("       T2.product_code as 'Product code' -- Base product code ");
		sql.appendln("from ( ");
		sql.appendln("select ");
		sql.appendln("x.year as 'Year', ");
		sql.appendln("x.month as 'Month', ");
		sql.appendln("x.year*100+x.month as 'YearMonth', ");
		sql
				.appendln("ISNULL(sa.company_name, x.reseller) as 'Reseller Comapny Name', ");
		sql.appendln("x.Product_Family as 'Product family', ");
		sql.appendln("x.Product_Category as 'Product category', ");
		sql.appendln("x.Product_Sub_Category as 'Product subcategory', ");
		sql.appendln("x.master_id as P_master_id, ");
		sql.appendln("x.sales_quantity as 'Qty', ");
		sql.appendln("x.sales_amount as 'Sales amount', ");
		sql
				.appendln("ISNULL(ba.company_name, x.Customer_Name) as 'End Customer Name', ");
		sql
				.appendln("ISNULL(ba.city, x.Customer_city) as 'End Customer City', ");
		sql
				.appendln("ISNULL(ba.state, x.End_Customer_state) as 'End Customer State', ");
		sql
				.appendln("ISNULL(ba.zipcode, x.End_Customer_zipcode) as 'End Customer zipcode', ");
		sql
				.appendln("ISNULL(ba.title1, 'Not Defined') as 'Industry Level 1', ");
		sql
				.appendln("ISNULL(ba.title2, 'Not Defined') as 'Industry Level 2', ");
		sql.appendln("ISNULL(ba.title3, 'Not Defined') as 'Industry Level 3' ");
		sql.appendln("from ");
		sql.appendln("( ");
		sql.appendln("select  ");
		sql.appendln("    s.period_year as year ");
		sql.appendln("    ,s.period_month as month ");
		sql.appendln("    ,s.seller_cid as seller ");
		sql.appendln("    ,sell_c.customer_name as reseller ");
		sql.appendln("    ,s.buyer_cid as buyer ");
		sql.appendln("    ,buy_c.customer_name as Customer_Name ");
		sql.appendln("    ,buy_c.city as Customer_city ");
		sql.appendln("    ,buy_c.state as End_Customer_state ");
		sql.appendln("    ,buy_c.zipcode as End_Customer_zipcode ");
		sql.appendln("    ,p.product_code ");
		sql.appendln("    ,p.master_id ");
		sql.appendln("    ,p.family as Product_Family ");
		sql.appendln("    ,p.category as Product_Category ");
		sql.appendln("    ,p.subcategory as Product_Sub_Category ");
		sql.appendln("    ,s.sales_quantity ");
		sql.appendln("    ,s.sales_amount ");
		sql
				.appendln("from SALES s, CUSTOMER sell_c, CUSTOMER buy_c, PRODUCT p ");

		sql.appendln("where   s.period_year = " + year);

		if (year == this_year) {
			sql.appendln("    and s.period_year* 100 + s.period_month <= "
					+ CubeCSV.max_sales_period + " ");
		}

		sql.appendln("    and s.tier=3 ");
		sql.appendln("    and s.seller_cid=sell_c.id ");
		sql.appendln("    and s.buyer_cid=buy_c.id ");
		sql.appendln("    and s.product_id=p.id ");
		sql.appendln("    and p.family is not NULL ");

		if (!product.equals("All"))
			sql.appendln("    and p.family = '" + product + "' ");

		sql.appendln(")x ");
		sql
				.appendln("left outer join custom_fact_nacis_all sa on x.seller = sa.customer_id ");
		sql
				.appendln("left outer join custom_fact_nacis_all ba on x.buyer = ba.customer_id  ");
		sql.appendln("--where ba.nacis is not NULL ");
		sql.appendln(") T1, ");
		sql.appendln("epos.dbo.product T2 ");
		sql.appendln("where T1.P_master_id = T2.id ");

		return sql.toString();
	}

	public static void buildCVSAllYearsT2(String tableName) {
		String sql = null;
		String file_name = null;
		for (int j = 0; j < products.length; j++) {
			sql = buildSql2(products[j], min_year);
			file_name = tableName + "_" + products[j] + "_" + min_year;

			buildCSV(file_name, sql);
		}

	}

	public static void buildCVSAllYearsT3(String tableName) {
		String sql = null;
		String file_name = null;
		for (int j = 0; j < products.length; j++) {

			sql = buildSql3(products[j], min_year);
			file_name = tableName + "_" + products[j] + "_" + min_year;

			buildCSV(file_name, sql);

		}
	}

	public static void buildCSV(String file_name, String sql) {
		System.out.println("==================================");
		System.out.println(file_name);
		System.out.println(sql);
		System.out.println();
		System.out.println();
		System.out.println("__________________________________");

		if (1 == 1)
			return;

		PreparedStatement ps;
		ResultSet rs;

		DBConnectorReal connector = new DBConnectorReal();

		try {
			connector.start();
			Connection connection = connector.getConnection();

			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();

			CSVGenerator generator = new CSVGenerator(new File("d:\\cube\\"
					+ file_name + ".csv"));
			generator.build(rs);
			generator.close();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			connector.close();
		}
		System.out.println("end");

	}
}
